package models.dados;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import play.db.DB;

import models.Administrador;
import models.Cliente;
import models.Donut;
import models.Endereco;
import models.Pedido;
import models.Produto;
import models.TipoUsuario;

public class MySQL implements DAO {
	
	private static MySQL instance;

	private Connection connection = null;

	private MySQL(){
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1/dm",
					"root", "123");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static DAO getInstancia(){
		if(instance==null){
			instance = new MySQL();
			return instance;
		}else{
			
			return instance;
		}
	}

	@Override
	public Cliente getClienteCadastrado(String nomeUsuario)
			throws DadoNaoEncontradoException {
		
		Cliente cliente = null;
		Connection c = DB.getConnection();
		try {
			Statement st = c.createStatement();
			ResultSet res = st.executeQuery("select * from dm.cliente where (login=\""+nomeUsuario+"\")");
			res.next();
			
			Endereco e = new Endereco(res.getString("endereco"), 0,"","","","");
			
			cliente = new Cliente(res.getString("nome"),res.getString("email"),e,
					e, res.getString("login"), res.getString("senha"), 
					res.getBoolean("ativo"), res.getBoolean("bloqueado"), 0, TipoUsuario.CLIENTE);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
		// TODO Auto-generated method stub
		return cliente;
	}

	@Override
	public Cliente getClienteNaoCadastrado(String nome)
			throws DadoNaoEncontradoException {
		
		Cliente cliente = null;
		Connection c = DB.getConnection();
		try {
			Statement st = c.createStatement();
			ResultSet res = st.executeQuery("select * from dm.cliente where (login=\""+nome+"\")");
			res.next();
			
			Endereco e = new Endereco(res.getString("endereco"), 0,"","","","");
			
			cliente = new Cliente(res.getString("nome"),res.getString("email"),e,
					e, "", "", 
					res.getBoolean("ativo"), res.getBoolean("bloqueado"), 0, TipoUsuario.CLIENTE_NAO_CADASTRADO);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
		return null;
	}
	
	public Cliente getClienteNaoCadastrado(long id)
			throws DadoNaoEncontradoException {
		Statement statement = null;
		ResultSet res = null;

		try {
			statement = connection.createStatement();

			statement.executeQuery("select * from cliente where cliente.id = "+id);

			res = statement.getResultSet();

			System.out.println(res.next());

			Endereco e = new Endereco(res.getString("endereco"), 0,"","","","");
			
			Cliente achado = new Cliente(
					res.getString("nome")
					,res.getString("email"),e,
					e, "", "", 
					res.getBoolean("ativo"), res.getBoolean("bloqueado"), 0, TipoUsuario.CLIENTE_NAO_CADASTRADO);
			return achado;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public Administrador getAdministrador(String nomeUsuario)
			throws DadoNaoEncontradoException, SQLException {
		Connection c = DB.getConnection();
		Statement st = c.createStatement();
		
		ResultSet res = st.executeQuery("select * from dm.adm where (login=\""+nomeUsuario+"\")");
		res.next();
		Administrador adm = new Administrador(res.getString("login"), res.getString("senha"),
				res.getBoolean("ativo"), res.getBoolean("bloqueado"), 3, TipoUsuario.ofString(res.getString("tipo")),"" );
		return adm;
	}

	@Override
	public Pedido getPedido(long numero) throws DadoNaoEncontradoException {
		
		Statement statement = null;
		ResultSet resultset = null;

		try {
			statement = connection.createStatement();

			statement.executeQuery("select * from pedido where pedido.id = "+numero);

			resultset = statement.getResultSet();

			System.out.println(resultset.next());

			Calendar c0 = new GregorianCalendar();
			Calendar c1 = new GregorianCalendar();

			DateFormat d  = DateFormat.getDateInstance(); 			

			try {
				c0.setTime(d.parse(resultset.getString("data")));
				c1.setTime(d.parse(resultset.getString("data")));
			} catch (ParseException e) {

				e.printStackTrace();
			}


			Pedido achado = new Pedido(
					getClienteNaoCadastrado(Long.parseLong(resultset.getString("id_cliente"))),
					c0,
					c1,
					Long.parseLong(resultset.getString("id")),
					null						
					);
			return achado;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public Produto getProduto(String nome) throws DadoNaoEncontradoException {
		Statement statement = null;
		ResultSet resultset = null;

		try {
			statement = connection.createStatement();

			statement.executeQuery("select * from donut where donut.sabor = '"+nome+"'");

			resultset = statement.getResultSet();

			System.out.println(resultset.next());



			Donut achado = new Donut(
					resultset.getString("sabor"),
					resultset.getString("sabor"),
					Double.parseDouble(resultset.getString("preco")),
					null,
					null,
					null
					);
			return achado;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void inserirCliente(Cliente cliente) throws ChaveRepetidaException {
		Statement statement = null;

		String insertStr = "insert into cliente(endereco, email, nome) values" +
				"("+cliente.getEnderecoEntrega().getRua()+","+cliente.getEmail() +","+
				cliente.getNome()+")";
		try{
			statement = connection.createStatement();
			statement.executeUpdate(insertStr);
			statement.close();

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void inserirPedido(Pedido pedido) throws ChaveRepetidaException {
		Statement statement = null;
		ResultSet resultset = null;

		try{

			statement = connection.createStatement();

			statement.executeQuery("select * from cliente where cliente.nome = '"+pedido.getCliente().getNome()+"'");

			resultset = statement.getResultSet();

			resultset.next();

			Long cliente_id = Long.parseLong(resultset.getString("id"));

			String insertStr = "insert into pedido(data, id_cliente) values "+
					"(" + pedido.getDataPedido().toString() + ", " + cliente_id.toString() + ")";

			statement = connection.createStatement();
			statement.executeUpdate(insertStr);
			statement.close();

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void inserirAdministrador(Administrador administrador)
			throws ChaveRepetidaException, SQLException {
		Statement statement = null;

		String insertStr = "insert into adm(login, senha) values" +
				"("+ administrador.getNomeUsuario() +","+administrador.getSenha()+ ")";
		try{
			statement = connection.createStatement();
			statement.executeUpdate(insertStr);
			statement.close();

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void inserir(Produto donut) throws ChaveRepetidaException,
			SQLException {
		String sabor = "\""+(donut.getNome())+"\",";
		String preco = "\""+String.valueOf(donut.getPreco())+"\",";
		String img = "\""+donut.getUrlImagem()+"\",";
		String ativo = "\"1\")";
		
		Connection c = DB.getConnection();
		Statement st = c.createStatement();
		st.execute("insert into dm.donut (sabor,preco,img,descricao,ativo) values ("+sabor+preco+img+"\"nenhuma\","+ativo);
		
		
		
		
		
	}

	@Override
	public void removerCliente(String nomeUsuario)
			throws DadoNaoEncontradoException {
		Statement statement = null;	

		String deleteStr = "delete from cliente where cliente.login = '"+nomeUsuario+"'";
		try{
			statement = connection.createStatement();
			statement.executeUpdate(deleteStr);
			statement.close();

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void removerPedido(Pedido pedido) throws DadoNaoEncontradoException {
		Statement statement = null;	

		String deleteStr = "delete from pedido where id = "+ Long.toString(pedido.getNumero());
		try{
			statement = connection.createStatement();
			statement.executeUpdate(deleteStr);
			statement.close();

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void removerAdministrador(String nomeUsuario)
			throws DadoNaoEncontradoException {
		Statement statement = null;	

		String deleteStr = "delete from adm where adm.login = '"+nomeUsuario+"'";
		try{
			statement = connection.createStatement();
			statement.executeUpdate(deleteStr);
			statement.close();

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void removerProduto(String tipo, String marca, String nome)
			throws DadoNaoEncontradoException {
		Statement statement = null;	

		String deleteStr = "delete from donut where donut.sabor = '"+nome+"'";
		try{
			statement = connection.createStatement();
			statement.executeUpdate(deleteStr);
			statement.close();

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void removerProduto(String nome) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void alterarCliente(Cliente cliente) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void alterarPedido(Pedido pedido) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void alterarAdministrador(Administrador administrador) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void alterarProduto(Produto produto) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Cliente> listarClientes(String nomeUsuario, String nome,
			Calendar dataCadastro, Calendar dataCadastroInicial,
			Calendar dataCadastroFinal, boolean bloqueado,
			boolean naobloqueado, boolean ehCadastrado, boolean naoCadastrado) {
		Statement statement = null;
		ResultSet resultset = null;

		List<Cliente> lista = new ArrayList<Cliente>();

		try {
			statement = connection.createStatement();

			statement.executeQuery("select * from cliente");

			resultset = statement.getResultSet();
			
			Endereco e = new Endereco(resultset.getString("endereco"), 0,"","","","");

			while(resultset.next()){

				Cliente achado = new Cliente(
						resultset.getString("nome"),
						resultset.getString("email"),
						e,
						e,
						resultset.getString("login"),
						resultset.getString("senha"),
						true,
						true,
						0,
						TipoUsuario.CLIENTE
						);


				lista.add( achado);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}


		return lista;
	}

	@Override
	public List<Administrador> listarAdministradores(String usuario,
			TipoUsuario tipo, String cadastradoPor, Calendar DataCadastro,
			boolean bloqueado, boolean naoBloqueado, boolean ativo,
			boolean inativo) {
		Statement statement = null;
		ResultSet resultset = null;

		List<Administrador> lista = new ArrayList<Administrador>(); 

		try {
			statement = connection.createStatement();

			statement.executeQuery("select * from adm");

			resultset = statement.getResultSet();

			while(resultset.next()){			

				Administrador achado = new Administrador(
						resultset.getString("login"),
						resultset.getString("senha"),
						true,
						true,
						1,
						TipoUsuario.ADM_SUPER, null
						);
				lista.add(achado);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}	    	  	   

		return lista;
	}

	@Override
	public List<Produto> listarProdutos() throws SQLException {
		Statement statement = null;
		ResultSet resultset = null;

		List<Produto> lista = new ArrayList<Produto>();

		try {
			statement = connection.createStatement();

			statement.executeQuery("select * from donut");

			resultset = statement.getResultSet();

			while(resultset.next()){

				Donut achado = new Donut(
						resultset.getString("sabor"),
						resultset.getString("sabor"),
						Double.parseDouble(resultset.getString("preco")),
						null,
						null,
						null
						);
				lista.add(achado);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lista;
	}

	@Override
	public List<Pedido> listarPedidos(long numero, String usuarioCliente,
			String nome, GregorianCalendar DataExata,
			GregorianCalendar dataInicial, GregorianCalendar dataFinal,
			String status, String usuarioUltimaAlteracao,
			GregorianCalendar dataUltimaAteracao) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Produto> listarProdutos(String tipo) {
		try {
			return listarProdutos();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
